package com.nf.dao.impl;

import com.nf.dao.FilmInfoDao;
import com.nf.entity.FilmInfo;
import com.nf.util.DBUtil;
import com.nf.util.PagerUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FilmInfoDaoImpl implements FilmInfoDao {
    @Override
    public List<FilmInfo> selectAll() {
        List<FilmInfo> list=new ArrayList<FilmInfo>();

        Connection conn= DBUtil.getConnection();
        String sql="select * from filmInfo";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            rst=pstmt.executeQuery();
            while (rst.next()){
                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String year=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int status=rst.getInt(10);
                String desc=rst.getString(11);

                FilmInfo film=new FilmInfo(id,name,type,director,language,filmLong,year,country,img,status,desc);
                list.add(film);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return list;
    }

    @Override
    public List<FilmInfo> selectShows() {
        List<FilmInfo> list=new ArrayList<FilmInfo>();

        Connection conn= DBUtil.getConnection();
        String sql="select * from filmInfo where film_status=1";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            rst=pstmt.executeQuery();
            while (rst.next()){
                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String year=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int status=rst.getInt(10);
                String desc=rst.getString(11);

                FilmInfo film=new FilmInfo(id,name,type,director,language,filmLong,year,country,img,status,desc);
                list.add(film);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return list;
    }

    @Override
    public List<FilmInfo> selectNotShows() {
        List<FilmInfo> list=new ArrayList<FilmInfo>();

        Connection conn= DBUtil.getConnection();
        String sql="select * from filmInfo where film_status=0";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            rst=pstmt.executeQuery();
            while (rst.next()){
                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String year=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int status=rst.getInt(10);
                String desc=rst.getString(11);

                FilmInfo film=new FilmInfo(id,name,type,director,language,filmLong,year,country,img,status,desc);
                list.add(film);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return list;
    }

    @Override
    public FilmInfo selectById(int filmId) {
        FilmInfo film=null;

        Connection conn= DBUtil.getConnection();
        String sql="select * from filmInfo where film_id=?";

        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,filmId);
            rst=pstmt.executeQuery();
            if (rst.next()){
                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String filmYear=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int status=rst.getInt(10);
                String desc=rst.getString(11);

                film=new FilmInfo(id,name,type,director,language,filmLong,filmYear,country,img,status,desc);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return film;
    }

    @Override
    public List<FilmInfo> selectByLike(String cond) {
        List<FilmInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from filmInFo where film_name like ?";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"%"+cond+"%");
            rst= pstmt.executeQuery();
            while (rst.next()){
                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String year=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int status=rst.getInt(10);
                String desc=rst.getString(11);

                FilmInfo film=new FilmInfo(id,name,type,director,language,filmLong,year,country,img,status,desc);
                list.add(film);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public int delete(int filmId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="delete from filmInfo where film_id=?";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,filmId);
            r= pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }
        return r;
    }

    @Override
    public List<FilmInfo> selectIsShowByLike(int status, String condtype, String condcountry) {
        List<FilmInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from filmInFo where film_status=? and film_type like ? and film_country like ?";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,status);
            pstmt.setString(2,"%"+condtype+"%");
            pstmt.setString(3,"%"+condcountry+"%");
            rst= pstmt.executeQuery();
            while (rst.next()){
                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String year=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int filmStatus=rst.getInt(10);
                String desc=rst.getString(11);

                FilmInfo film=new FilmInfo(id,name,type,director,language,filmLong,year,country,img,filmStatus,desc);
                list.add(film);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public int updateStatus(int status,int filmId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="update filmInfo set film_status=? where film_id=?";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,status);
            pstmt.setInt(2,filmId);

            r=pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }
        return r;
    }

    @Override
    public PagerUtil<FilmInfo> selectAllByPager(int currentPage, int pageCount) {
        // 创建分页对象，并初始化分页的信息
        PagerUtil<FilmInfo> pager = new PagerUtil<FilmInfo>();

        // 封装总记录数
        pager.setTotalRecords(getCount());
        // 封装每页显示的记录数
        pager.setPageCount(pageCount);
        // 封装当前页
        pager.setCurrentPage(currentPage);

        List<FilmInfo> list =new  ArrayList<FilmInfo>();

        String sql = "select * from ( select *, row_number() over(order by a.film_id asc) as rownumber from filmInfo as a) as b where rownumber between  ? and ?" ;
        Connection conn = DBUtil.getConnection();

        PreparedStatement pstmt = null ;
        ResultSet rst = null ;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (currentPage - 1) * pageCount + 1);
            pstmt.setInt(2, currentPage * pageCount);
            rst = pstmt.executeQuery();

            while (rst.next()) {

                int id=rst.getInt(1);
                String name=rst.getString(2);
                String type=rst.getString(3);
                String director=rst.getString(4);
                String language=rst.getString(5);
                int filmLong=rst.getInt(6);
                String year=rst.getString(7);
                String country=rst.getString(8);
                String img=rst.getString(9);
                int status=rst.getInt(10);
                String desc=rst.getString(11);

                FilmInfo film=new FilmInfo(id,name,type,director,language,filmLong,year,country,img,status,desc);
                list.add(film);
            }

            // 把当前页中的数据，封装起来
            pager.setData(list);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBUtil.close(rst, pstmt, conn);
        }

        return pager;
    }

    @Override
    public int getCount() {
        int r = 0 ;

        String sql ="select COUNT(film_id) from filmInfo";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst =null;
        try {
            pstmt = conn.prepareStatement(sql);
            rst = pstmt.executeQuery();
            if(rst.next()) {

                r=rst.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rst, pstmt, conn);
        }
        return r;
    }
}
